/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package jcac_1_0.v.reportexport;

import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.GregorianCalendar;
import java.util.HashMap;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.swing.JFileChooser;
import javax.swing.JOptionPane;
import jcac_1_0.util.AppConf;
import jcac_1_0.util.DbUtil;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.util.CellRangeAddress;
import sochishun.ucl.ExcelFileFilter;

/**
 *
 * @author SoChishun
 */
public class FrmMenjinReportExport extends javax.swing.JFrame {

    /**
     * Creates new form FrmMenjinReportExport
     */
    public FrmMenjinReportExport() {
        initComponents();
        setClickEvent();
    }

    void setClickEvent() {
        btn_save.addActionListener(new ActionListener() {
            @Override
            public void actionPerformed(ActionEvent e) {
                JFileChooser jfc = new JFileChooser();
                jfc.setFileFilter(new ExcelFileFilter());
                //jfc.setCurrentDirectory(fileFlag);// 设置打开对话框的默认路径
                jfc.setSelectedFile(new File("aa.xls"));// 设置选中原来的文件
                int result = jfc.showSaveDialog(null);
                if (result == JFileChooser.APPROVE_OPTION) {
                    try {
                        saveExcel(jfc.getSelectedFile());
                        if (JOptionPane.OK_OPTION == JOptionPane.showConfirmDialog(null, "保存成功, 是否打开目录!")) {
                            Runtime.getRuntime().exec("cmd.exe /c start explorer " + jfc.getSelectedFile().getParent().toString());
                        }
                    } catch (Exception ex) {
                        Logger.getLogger(FrmMenjinReportExport.class.getName()).log(Level.SEVERE, null, ex);
                    }
                }
            }
        });
    }

    void saveExcel(File file) throws FileNotFoundException, IOException {
        String ym = "2016-03";
        int dptid = 0; // 5

        String[] aym = ym.split("-");
        int year = Integer.parseInt(aym[0]), month = Integer.parseInt(aym[1]);
        GregorianCalendar gCalendar = new GregorianCalendar();
        gCalendar.set(year, month, 1);
        int days = gCalendar.getActualMaximum(GregorianCalendar.DAY_OF_MONTH);
        int leave_count = AppConf.LeaveClassInfos.size(), nrow, ncol;
        int col_count = days + leave_count + 3; // 3=部门, 姓名, 时间

        // ===============实例化Excel对象==================  
        // 创建excel
        HSSFWorkbook wb = new HSSFWorkbook();
        // 创建sheet
        HSSFSheet sheet = wb.createSheet(ym);
        // ===============/实例化Excel对象==================
        // ==============表头区域========================
        // 表头标题行(第一行)
        HSSFRow rowTitle = sheet.createRow(0);
        HSSFCell cellTitle = rowTitle.createCell(0);
        // 创建标题栏样式
        HSSFCellStyle styleTitle = wb.createCellStyle();
        styleTitle.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 居中
        HSSFFont fontTitle = wb.createFont();
        // 宋体加粗
        fontTitle.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        fontTitle.setFontName("微软雅黑");
        fontTitle.setFontHeight((short) 400);
        styleTitle.setFont(fontTitle);
        cellTitle.setCellStyle(styleTitle);
        cellTitle.setCellValue(AppConf.COMPANY_NAME + "职工门禁报表");
        sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, col_count)); // 合并单元格
        // 表头表格日期行(第二行)
        sheet.createRow(1).createCell(0).setCellValue("日期：" + ym);
        sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, col_count)); // 合并单元格
        // 表头月份日期行(第三行)
        HSSFRow rowHead = sheet.createRow(2);
        rowHead.createCell(0).setCellValue("部门");
        rowHead.createCell(1).setCellValue("姓名");
        rowHead.createCell(2).setCellValue("日期");
        for (int i = 1; i <= days; i++) {
            rowHead.createCell((2 + i)).setCellValue(i);
        }
        // 请假统计
        for (int i = 0; i < leave_count; i++) {
            rowHead.createCell((3 + i + days)).setCellValue((AppConf.LeaveClassInfos.get(i)).text);
        }
        // 表头月份星期行(第四行)cal.setTime(date);
        HSSFRow rowHead2 = sheet.createRow(3);
        HSSFCell cell;
        HSSFCellStyle weekendStyle = wb.createCellStyle();
        HSSFFont weekendFont = wb.createFont();
        weekendFont.setColor(HSSFColor.GREEN.index); // 设置颜色(常见色彩：1:Black; 2:White; 3:Red; 4:Green; 5:Blue; 6:Yellow; 7:Pink; 9:DarkRed; 10:DarkGreen; 16:Grey; 21:Purple)
        weekendFont.setBold(true);
        weekendStyle.setFont(weekendFont);
        String[] cnweeks = new String[]{"天", "一", "二", "三", "四", "五", "六"};
        int dayofweek;
        for (int i = 1; i <= days; i++) {
            gCalendar.set(year, month, i);
            dayofweek = gCalendar.get(GregorianCalendar.DAY_OF_WEEK) - 1;
            if (dayofweek < 0) {
                dayofweek = 0;
            }
            cell = rowHead2.createCell((3 + i));
            cell.setCellValue(cnweeks[dayofweek]);
            if (dayofweek == 0 || dayofweek == 6) {
                cell.setCellStyle(weekendStyle);
            }
        }
        // 合并第三行和第四行
        sheet.addMergedRegion(new CellRangeAddress(2, 3, 0, 0));
        sheet.addMergedRegion(new CellRangeAddress(2, 3, 1, 1));
        sheet.addMergedRegion(new CellRangeAddress(2, 3, 2, 2));
        // 请假统计二行合一
        for (int i = 0; i < leave_count; i++) {
            ncol = days + 3 + i;
            sheet.addMergedRegion(new CellRangeAddress(2, 3, ncol, ncol));
        }
        // ==============/表头区域========================

        // ==================内容数据======================
        String where = "";
        if (dptid > 0) {
            where += "u.department_ids='" + dptid + "'";
        }
        where += (where.length() > 0 ? " and " : "") + "u.type_name='EMPLOYEE'";
        List<HashMap> dtMembers = DbUtil.executeQueryList("select u.name, u.attendance_no, u.department_ids, ifnull(d.title,'') as department_names from t_porg_user u left join think_auth_department d on u.department_ids=d.id where " + where + " order by u.department_ids, u.attendance_no");
        List<HashMap> dayreport, monthreport;
        int irow = 4, iday = 3;
        int leave_n;
        HashMap rmonthreport;
        String cellvalue, dptname, name, attendance_no, leave_id;
        HSSFRow row;
        // 红色样式
        HSSFCellStyle redStyle = wb.createCellStyle();
        HSSFFont redFont = wb.createFont();
        redFont.setColor(HSSFColor.RED.index);
        redStyle.setFont(redFont);
        for (HashMap rmember : dtMembers) {
            attendance_no = rmember.get("attendance_no").toString();
            // 查询单个考勤号对应的月份记录(30条,从1号排序到30号)
            dayreport = DbUtil.executeQueryList("select time_ymd,am_s,am_e,pm_s,pm_e,time_am_s,time_am_e,time_pm_s,time_pm_e,status_am_s,status_am_e,status_pm_s,status_pm_e, status_am, status_pm from t_porg_attendance_report_detail where time_ym='" + ym + "' and attendance_no='" + attendance_no + "' order by time_day");
            // 查询单个考勤号每月考勤统计(1条记录)
            monthreport = DbUtil.executeQueryList("select * from t_porg_attendance_report where time_ym='" + ym + "' and attendance_no='" + attendance_no + "'");
            if (null == monthreport || monthreport.size() < 1) {
                continue;
            }
            rmonthreport = monthreport.get(0);
            // 每人每天4条记录
            // 早上上班一行
            dptname = null == rmember.get("department_names") ? "" : rmember.get("department_names").toString();
            name = null == rmember.get("name") ? "" : rmember.get("name").toString();
            row = sheet.createRow(irow);
            row.createCell(0).setCellValue(dptname);
            row.createCell(1).setCellValue(name);
            row.createCell(2).setCellValue(1);
            iday = 3;
            for (HashMap rreportday : dayreport) {
                // 早上上班,请假是按0.5天计算的,所以只要有请假，早上打卡不计，都算请假
                cellvalue = null == rreportday.get("am_s") ? "" : rreportday.get("am_s").toString();
                cell = row.createCell(iday);
                cell.setCellValue(cellvalue);
                // 标记异常颜色
                if (cellvalue.contains(":") && rreportday.get("status_am_s").toString() == "Y") {
                    cell.setCellStyle(redStyle);
                }
                iday++;
            }
            // 每人当月请假统计,数据库存的是次数(半天)，实际显示要乘以0.5
            for (int i = 0; i < leave_count; i++) {
                leave_id = AppConf.LeaveClassInfos.get(i).id;
                leave_n = Integer.parseInt(rmonthreport.get(leave_id).toString());
                row.createCell((2 + i + days)).setCellValue((leave_n * 0.5));
            }
            irow++;
            // 早上下班一行
            row = sheet.createRow(irow);
            row.createCell(2).setCellValue(2);
            iday = 3;
            for (HashMap rreportday : dayreport) {
                // 早上下班
                cellvalue = null == rreportday.get("am_e") ? "" : rreportday.get("am_e").toString();
                cell = row.createCell(iday);
                cell.setCellValue(cellvalue);
                // 标记异常颜色
                if (cellvalue.contains(":") && rreportday.get("status_am_e").toString() == "Y") {
                    cell.setCellStyle(redStyle);
                }
                iday++;
            }
            irow++;
            // 下午上班一行
            row = sheet.createRow(irow);
            row.createCell(2).setCellValue(3);
            iday = 3;
            for (HashMap rreportday : dayreport) {
                // 下午上班
                cellvalue = null == rreportday.get("pm_s") ? "" : rreportday.get("pm_s").toString();
                cell = row.createCell(iday);
                cell.setCellValue(cellvalue);
                // 标记异常颜色
                if (cellvalue.contains(":") && rreportday.get("status_pm_s").toString() == "Y") {
                    cell.setCellStyle(redStyle);
                }
                iday++;
            }
            irow++;
            // 下午下班一行
            row = sheet.createRow(irow);
            row.createCell(2).setCellValue(4);
            iday = 3;
            for (HashMap rreportday : dayreport) {
                // 下午下班
                cellvalue = null == rreportday.get("pm_e") ? "" : rreportday.get("pm_e").toString();
                cell = row.createCell(iday);
                cell.setCellValue(cellvalue);
                // 标记异常颜色
                if (cellvalue.contains(":") && rreportday.get("status_pm_e").toString() == "Y") {
                    cell.setCellStyle(redStyle);
                }
                iday++;
            }
            // 合并单元格
            if (irow > 7 && ((irow - 4) % 4 < 1)) {
                nrow = irow - 3;
                sheet.addMergedRegion(new CellRangeAddress(nrow, irow, 0, 0));
                sheet.addMergedRegion(new CellRangeAddress(nrow, irow, 1, 1));
                for (int ileave = 0; ileave < leave_count; ileave++) {
                    sheet.addMergedRegion(new CellRangeAddress(nrow, irow, 2 + days + ileave, 2 + days + ileave));
                }
            }
            irow++;
        }
        // ==================/内容数据====================
        // ==================释放资源=====================
        // 保存到文件            
        FileOutputStream fout = new FileOutputStream(file);
        // 关闭资源
        wb.write(fout);
        fout.close();
        wb.close();
        // ==================/释放资源=====================
    }

    /*
    /**
     * This method is called from within the constructor to initialize the form. WARNING: Do NOT modify this code. The content of this method is always regenerated by the Form Editor.
     */
    @SuppressWarnings("unchecked")
    // <editor-fold defaultstate="collapsed" desc="Generated Code">//GEN-BEGIN:initComponents
    private void initComponents() {

        btn_save = new javax.swing.JButton();

        setDefaultCloseOperation(javax.swing.WindowConstants.EXIT_ON_CLOSE);

        btn_save.setMnemonic('E');
        btn_save.setText("导出(E)");

        javax.swing.GroupLayout layout = new javax.swing.GroupLayout(getContentPane());
        getContentPane().setLayout(layout);
        layout.setHorizontalGroup(
            layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addGroup(javax.swing.GroupLayout.Alignment.TRAILING, layout.createSequentialGroup()
                .addContainerGap(201, Short.MAX_VALUE)
                .addComponent(btn_save)
                .addGap(124, 124, 124))
        );
        layout.setVerticalGroup(
            layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addGroup(javax.swing.GroupLayout.Alignment.TRAILING, layout.createSequentialGroup()
                .addContainerGap(174, Short.MAX_VALUE)
                .addComponent(btn_save)
                .addGap(103, 103, 103))
        );

        pack();
    }// </editor-fold>//GEN-END:initComponents

    /**
     * @param args the command line arguments
     */
    public static void main(String args[]) {
        /* Set the Nimbus look and feel */
        //<editor-fold defaultstate="collapsed" desc=" Look and feel setting code (optional) ">
        /* If Nimbus (introduced in Java SE 6) is not available, stay with the default look and feel.
         * For details see http://download.oracle.com/javase/tutorial/uiswing/lookandfeel/plaf.html 
         */
        try {
            for (javax.swing.UIManager.LookAndFeelInfo info : javax.swing.UIManager.getInstalledLookAndFeels()) {
                if ("Nimbus".equals(info.getName())) {
                    javax.swing.UIManager.setLookAndFeel(info.getClassName());
                    break;
                }
            }
        } catch (ClassNotFoundException ex) {
            java.util.logging.Logger.getLogger(FrmMenjinReportExport.class.getName()).log(java.util.logging.Level.SEVERE, null, ex);
        } catch (InstantiationException ex) {
            java.util.logging.Logger.getLogger(FrmMenjinReportExport.class.getName()).log(java.util.logging.Level.SEVERE, null, ex);
        } catch (IllegalAccessException ex) {
            java.util.logging.Logger.getLogger(FrmMenjinReportExport.class.getName()).log(java.util.logging.Level.SEVERE, null, ex);
        } catch (javax.swing.UnsupportedLookAndFeelException ex) {
            java.util.logging.Logger.getLogger(FrmMenjinReportExport.class.getName()).log(java.util.logging.Level.SEVERE, null, ex);
        }
        //</editor-fold>

        /* Create and display the form */
        java.awt.EventQueue.invokeLater(new Runnable() {
            public void run() {
                new FrmMenjinReportExport().setVisible(true);
            }
        });
    }

    // Variables declaration - do not modify//GEN-BEGIN:variables
    private javax.swing.JButton btn_save;
    // End of variables declaration//GEN-END:variables
}
